﻿package com.kingtone.rongji.db;

import java.util.ArrayList;
import java.util.List;

import com.kingtone.rongji.biz.Region;
import com.kingtone.rongji.tool.AppContasts;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class RegionDao {
	
	private String TAG = RegionDao.class.getSimpleName();///<和会议相关的数据库表操作类
	
	private DataBaseOpenHelper databaseHelper = null; ///<通过该对象获取操作数据库的对象

	public SQLiteDatabase db = null;
	
	/**
	 * @brief 构造方法
	 * @param context   上下文Context对象
	 */
	public RegionDao(Context context) 
	{
		this.databaseHelper = new DataBaseOpenHelper(context);
	}
	
	public void addRegionsToDB(List<Region> regions)
	{
		try
		{
			db = databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
			{
				db.beginTransaction(); 
				for(Region region: regions)
				{
					ContentValues values = new ContentValues();
					values.put(AppContasts.REGION_ID,  region.getRegionId());
					values.put(AppContasts.REGION_NAME, region.getRegionName());
					String dbName = null;
					switch(region.getDepth())
					{
						case 1:
							dbName = AppContasts.PROVINCE_TABLE;
							break;
						case 2:
							dbName = AppContasts.CITY_TABLE;
							values.put(AppContasts.PARENT_ID, region.getParentId());
							break;
						case 3:
							dbName = AppContasts.COUNTY_TABLE;
							values.put(AppContasts.PARENT_ID, region.getParentId());
							break;
					}
					db.insert(dbName, null, values);
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}catch(Exception e)
		{
			Log.e(TAG, "addProvinceToDB() error " + e.getMessage());
		}
	}
	
	public synchronized String getProvinceByRegionId(int regionId)
	{
		String province = null;
		try
		{
			db=databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
			{
				 String sql = "select " + AppContasts.REGION_NAME + " from " 
			                + AppContasts.PROVINCE_TABLE + " where " 
							+ AppContasts.REGION_ID + "  = " + regionId;
			    Cursor cursor = db.rawQuery(sql, null);
			    if (cursor.moveToNext())
			    {
			    	province = cursor.getString(0);
			    }
			    cursor.close();
			    db.close();
			}
		}catch(Exception e)
		{
			Log.e(TAG, "addRegionToDB() error " + e.getMessage());
		}
		return province;

	}
	
	/*
	 * 获取省级信息
	 */
	public synchronized ArrayList<Region> findAllProvince()
	{
		ArrayList<Region> pList = new ArrayList<Region>();
		try
		{
			db=databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
			{
				
				String sql = "select * from " + AppContasts.PROVINCE_TABLE;
			    Cursor cursor = db.rawQuery(sql, null);
			    while (cursor.moveToNext())
			    {
			    	pList.add(getRegion(cursor));
			    }
			    cursor.close();
			    db.close();
			}
		}catch(Exception e)
		{
			Log.e(TAG, "addRegionToDB() error " + e.getMessage());
		}
		return pList;

	}
	
	/**
	 * 根据parentId获取城市列表
	 * @param parentId
	 * @return
	 */
	public synchronized ArrayList<Region> findCityesByParentId(int parentId)
	{
		ArrayList<Region> pList = new ArrayList<Region>();
		try
		{
			db=databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
			{
				
				String sql = "select * from " + AppContasts.CITY_TABLE + " where " + AppContasts.PARENT_ID +" = " + parentId;
			    Cursor cursor = db.rawQuery(sql, null);
			    while (cursor.moveToNext())
			    {
			    	pList.add(getRegion(cursor));
			    }
			    cursor.close();
			    db.close();
			}
		}catch(Exception e)
		{
			Log.e(TAG, "addRegionToDB() error " + e.getMessage());
		}
		return pList;
	}
	
	/**
	 * 根据parentId获取县区列表
	 * @param parentId
	 * @return
	 */
	public synchronized ArrayList<Region> findCountiesByParentId(int parentId)
	{
		ArrayList<Region> pList = new ArrayList<Region>();
		try
		{
			db=databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
			{
				
				String sql = "select * from " + AppContasts.COUNTY_TABLE  + " where " + AppContasts.PARENT_ID +" = " + parentId;
			    Cursor cursor = db.rawQuery(sql, null);
			    while (cursor.moveToNext())
			    {
			    	pList.add(getRegion(cursor));
			    }
			    cursor.close();
			    db.close();
			}
		}catch(Exception e)
		{
			Log.e(TAG, "addRegionToDB() error " + e.getMessage());
		}
		return pList;

	}
	
	private Region getRegion(Cursor cursor)
	{
		Region region = new Region();
		region.setRegionId(cursor.getInt(cursor.getColumnIndex(AppContasts.REGION_ID)));
		region.setRegionName(cursor.getString(cursor.getColumnIndex(AppContasts.REGION_NAME)));
		if(cursor.getColumnCount() == 4)
		{
			region.setParentId(cursor.getInt(cursor.getColumnIndex(AppContasts.PARENT_ID)));
		}
		return region;
	}
	
	public synchronized int  getProvinceRegionIdByParentId(int parentId)
	{
		int pId = 0;
		try
		{
			db=databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
			{
				
				String sql = "select "+ AppContasts.PARENT_ID + " from " + AppContasts.COUNTY_TABLE  + " where " + AppContasts.REGION_ID +" = " + parentId;
			    Cursor cursor = db.rawQuery(sql, null);
			    if (cursor.moveToNext())
			    {
			    	int cId = cursor.getInt(cursor.getColumnIndex(AppContasts.PARENT_ID));
			    	if(cId > 0)
			    	{
			    		String subSql = "select "+ AppContasts.PARENT_ID + " from " + AppContasts.CITY_TABLE  + " where " + AppContasts.REGION_ID +" = " + cId;
			    		cursor = db.rawQuery(subSql, null);
						 if (cursor.moveToNext())
						 {
							 pId = 	cursor.getInt(cursor.getColumnIndex(AppContasts.PARENT_ID));
						 }
			    	}
			    	
			    }
			    cursor.close();
			    db.close();
			}
		}catch(Exception e)
		{
			Log.e(TAG, "getProvinceRegionIdByParent() error " + e.getMessage());
		}
		return pId;

	}
	
	public synchronized int  getCityRegionIdByParentId(int parentId)
	{
		int cId = 0;
		try
		{
			db=databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
			{
				
				String sql = "select "+ AppContasts.PARENT_ID + " from " + AppContasts.COUNTY_TABLE  + " where " + AppContasts.REGION_ID +" = " + parentId;
			    Cursor cursor = db.rawQuery(sql, null);
			    if (cursor.moveToNext())
			    {
			    	cId = cursor.getInt(cursor.getColumnIndex(AppContasts.PARENT_ID));
			    }
			    cursor.close();
			    db.close();
			}
		}catch(Exception e)
		{
			Log.e(TAG, "getProvinceRegionIdByParent() error " + e.getMessage());
		}
		return cId;

	}
	
	/**
	 * 删除省市县表
	 */
	public synchronized void deleteRegionTable()
	{
		try
		{
			db=databaseHelper.getWritableDatabase();
			if(db.isOpen() && databaseHelper.isDbNotLock(db))
		    {
		    	db.execSQL("delete from " + AppContasts.PROVINCE_TABLE);
		    	db.execSQL("delete from " + AppContasts.CITY_TABLE);
		    	db.execSQL("delete from " + AppContasts.COUNTY_TABLE);
		    	db.close();
		    }
		}catch(Exception e)
		{
			Log.e(TAG, "deleteRegionTable() error " + e.getMessage());
		}
		
	}
}
